IF EXISTS (
    SELECT
      1
    FROM sysobjects
    WHERE id = OBJECT_ID('sync_ClientPro_Card_back')
      AND type IN ('P', 'PC')
  )
  DROP PROCEDURE sync_ClientPro_Card_back

GO

/*
1.20200509 lrh调整退卡兼容虚拟卡，退卡时处理dt_ac_link
*/
create  procedure [dbo].[sync_ClientPro_Card_back] 	--退卡处理过程
(
@user_serial bigint,		 --人员序号
@card_serial char(8),		 --卡顺序号
@card_hao varchar(20),	 	 --物理卡号
@card_verify varchar(20),	 --卡片认证码
@card_form varchar(20),		 --卡片目录
@card_lx int,			 	 --卡类型
@card_type int,				 --卡状态
@lx int = 0,					--过程类型（停用）
@ip varchar(20),			 --ip
@gly_no nvarchar(50),		 --管理员
@reg_serial varchar(20),	 --企业编号
@version_no int=0			 --卡片当前版本号
)
as

set nocount on

---变量定义
declare @xh int
declare @readd_count int
declare @ac_money money
declare @user_no varchar(100)
declare @dep_Serial bigint
declare @user_regserial varchar(20)
declare @ac_addo money
declare @ac_subo money
declare @ac_eacho money
declare @old_cash_ye money
declare @old_each_ye money
declare @old_subsidy_ye money
declare @non_save money
declare @non_sub money
declare @card_hao_edit varchar(20)
declare @card_serial_edit char(8)

set @readd_count=0

	select @user_no = user_no from dt_user where user_serial = @user_serial

	select @card_serial = card_serial
	from dt_card where card_hao= @card_hao

	select @ac_money=ac_money from dt_ac_link where user_serial=@user_serial
	if(@ac_money<>0)
	begin

		select 3	--账户存在余额,先退款处理
		print '账户存在余额,先退款处理,工号:'+@user_no
		return

	end


	--0.生成预退卡日志
	insert into wt_card_log(
	lx,log_type,log_state,user_serial,card_old,
	card_new,log_xh,log_sj,log_ip,gly_no,log_lx,log_group,
	log_row,log_erro,bz,regserial
	)values(
	13,0,0,@user_serial,@card_hao,
	@card_serial,@card_lx,getdate(),@ip,@gly_no,0,null,
	null,0,null,@reg_serial
	)

	--1.生成dt_card_temp记录
	insert into dt_card_temp(lx,user_serial,gly_no,card_lx,card_hao,card_serial,card_verify,card_type,card_order,sj)
	select lx,user_serial,gly_no,card_lx,@card_hao,@card_serial,@card_verify,@card_type,card_order,GETDATE()
	from dt_card
	where user_serial = @user_serial
	and card_serial = @card_serial
	and card_hao = @card_hao

	--1.删除转换卡数据
	delete a from DT_CARD_USER a,dt_card b where a.Parent_xh=b.xh and b.user_serial = @user_serial and b.card_serial = @card_serial

	--2.删除卡表数据
	delete from dt_card where user_serial = @user_serial and card_serial = @card_serial

	--3.更新人员表数据、dt_ac_link表
		if(select count(1) from dt_card where user_serial=@user_serial and isnull(is_virtual_card ,0)=0  and card_type=0)>0
		begin
			select @card_hao_edit=card_hao,@card_serial_edit=card_serial from dt_card where user_serial=@user_serial and isnull(is_virtual_card ,0)=0  and card_type=0

			--更新人员表数据
			update dt_user
			set user_card=@card_hao_edit
			where user_serial = @user_serial
			and user_card = @card_hao

			--更新dt_ac_link表
			update dt_ac_link
			set card_hao=@card_hao_edit,card_serial=@card_serial_edit
			where user_serial=@user_serial
		end
		else
		begin
			select @card_hao_edit=card_hao,@card_serial_edit=card_serial from dt_card where user_serial=@user_serial and isnull(is_virtual_card ,0)=1

			--更新人员表数据
			update dt_user
			set user_card=cast(user_serial  as varchar(50))
			where user_serial = @user_serial
			and user_card = @card_hao

			--更新dt_ac_link表
			update dt_ac_link
			set card_hao=@card_hao_edit,card_serial=@card_serial_edit
			where user_serial=@user_serial
		end

	--4.更新人员账户信息
	update dt_ac_user
	set ac_eacho=a.ac_eacho-b.ac_eacho
	from dt_ac_user a,dt_ac_card b
	where a.user_serial=b.user_serial
	and b.user_serial = @user_serial
	and b.card_serial = @card_serial

	--5.更新人员卡户信息
	update dt_ac_card
	set ac_state=2,ac_eacho=0
	where user_serial = @user_serial
	and card_serial = @card_serial

	--生成增量更新数据20151021
	insert into wt_public(lx,log_type,is_all,user_serial,card_serial,log_sj,log_ip,gly_no,new_number,old_number)
	values(8,8,0,@user_serial,@card_serial,getdate(),@ip,@gly_no,0,0)

	--正常卡退卡&挂失卡退卡(未补卡)

	-- insert into WT_USER_UP(lx,log_type,card_serial,user_serial,log_sj,log_ip,gly_no)
	-- values(2,1,@card_Serial,@user_serial,getdate(),@ip,@gly_no)


	---人员是否离职（离职人员进行销户）
	--if exists(select 1 from dt_user where user_serial=@user_serial and user_type=51)
	--begin
	--
	--	--更新人员账户为初始状态
	--	update dt_ac_user set ac_type='0000000000000001',ac_pass='123456',ac_jssj=getdate(),ac_state=1,sj=getdate(),Ac_addm=0,Ac_subm=0,Ac_regm=0,Ac_make=0,Ac_eachm=0 where user_serial = @user_serial
	--	--更新人员表账户状态为0
	--	update dt_user set user_ac=0 where user_serial=@user_serial
	--
	--end

	--6.更新退卡日志状态
	update WT_CARD_LOG
	set lx=5,card_old = @card_hao,log_xh = @card_lx,
		log_sj = getdate(),log_ip =@ip,gly_no=@gly_no
	where lx=13
	and user_serial = @user_serial
	and card_new = @card_serial


GO

